﻿using Common.Infrastructure;
using Common.RunModels.File;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace File.Api.Utils
{
    public static class ExcelToDataUtils
    {
        public static ExcelByDataModel ExcelImportToExcelData(Stream inputStream, List<string> sheetNames, List<int> sheetIndexs)
        {
            ExcelByDataModel ret = new ExcelByDataModel();
            IWorkbook wb = WorkbookFactory.Create(inputStream); 
            for (int i = 0; i < wb.NumberOfSheets; i++)
            {
                ISheet sheet = wb.GetSheetAt(i);
                if (sheet == null)
                {
                    continue;
                }

                var sheetName = sheet.SheetName?.Trim();
                if (sheetNames != null && !sheetNames.Contains(sheetName))
                {
                    continue;
                }

                if (sheetIndexs != null && !sheetIndexs.Contains(i))
                {
                    continue;
                }

                ExcelByDataTableModel table = new ExcelByDataTableModel() { TableIndex = i, Name = sheetName };
                ret.Tables.Add(table);

                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                int startIndex = -1;

                while (rows.MoveNext())
                {
                    IRow row = (IRow)rows.Current;
                    ExcelByDataRowModel rowData = new ExcelByDataRowModel();
                    table.Rows.Add(rowData);

                    var tempStartIndex = startIndex;
                    if (tempStartIndex == -1)
                    {
                        tempStartIndex = row.FirstCellNum;
                        startIndex = row.FirstCellNum;
                    }

                    for (var jj = tempStartIndex; jj < row.FirstCellNum; jj++)
                    {
                        rowData.Datas.Add(string.Empty);
                    }

                    for (var j = tempStartIndex; j < row.LastCellNum; ++j)
                    {
                        string o = string.Empty;
                        ICell cell = row.GetCell(j);

                        if (cell != null)
                        {
                            var s = GetCellValue(cell);
                            if (s != null)
                            {
                                o = LibSysUtils.ToString(s);
                            }
                        }

                        rowData.Datas.Add(o);
                    }
                }
            }

            return ret;
        }

        public static ExcelByDataModel ExcelImportToExcelDataByCVS(Stream inputStream)
        {
            ExcelByDataModel ret = new ExcelByDataModel();
            ExcelByDataTableModel table = new ExcelByDataTableModel();
            ret.Tables.Add(table);

            string line = null;

            using (StreamReader reader = new StreamReader(inputStream, Encoding.UTF8))
            {
                while ((line = reader.ReadLine()) != null)
                {
                    if (string.IsNullOrEmpty(line))
                    {
                        continue;
                    }

                    var psts = new List<string>();
                    string val = string.Empty;
                    var type = 0;
                    foreach (var p in line)
                    {
                        if (p == '"')
                        {
                            if (type == 0)
                            {
                                type = 1;
                            }
                            else
                            {
                                type = 0;
                            }
                        }
                        else if (p == ',' && type == 0)
                        {
                            psts.Add(val);
                            val = string.Empty;
                        }
                        else if (string.IsNullOrEmpty(val))
                        {
                            val += p;
                        }
                        else
                        {
                            val += p;
                        }
                    }

                    psts.Add(val);

                    table.Rows.Add(new ExcelByDataRowModel() { Datas = psts });
                }
            }

            return ret;          
        }

        public static object GetCellValue(ICell item)
        {
            if (item == null)
            {
                return string.Empty;
            }

            switch (item.CellType)
            {
                case CellType.Boolean:
                    return item.BooleanCellValue;

                case CellType.Error:
                    return ErrorEval.GetText(item.ErrorCellValue);

                case CellType.Formula:
                    switch (item.CachedFormulaResultType)
                    {
                        case CellType.Boolean:
                            return item.BooleanCellValue;

                        case CellType.Error:
                            return ErrorEval.GetText(item.ErrorCellValue);

                        case CellType.Numeric:
                            if (DateUtil.IsCellDateFormatted(item))
                            {
                                return item.DateCellValue.ToString("yyyy-MM-dd");
                            }
                            else if (item.CellStyle != null && item.CellStyle.DataFormat == 58)
                            {
                                return GetCellValueByDate(item);
                            }
                            else if (item.CellStyle != null && item.CellStyle.DataFormat == 176)
                            {
                                return GetCellValueByDate(item);
                            }
                            else
                            {
                                return item.NumericCellValue;
                            }

                        case CellType.String:
                            string str = item.StringCellValue;
                            if (!string.IsNullOrEmpty(str))
                            {
                                return str.ToString();
                            }
                            else
                            {
                                return string.Empty;
                            }

                        case CellType.Unknown:
                        case CellType.Blank:
                        default:
                            return string.Empty;
                    }

                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(item))
                    {
                        return item.DateCellValue.ToString("yyyy-MM-dd");
                    }
                    else if (DateUtil.IsCellInternalDateFormatted(item))
                    {
                        return item.DateCellValue.ToString("yyyy-MM-dd");
                    }
                    else if (item.CellStyle != null && item.CellStyle.DataFormat == 58)
                    {
                        return GetCellValueByDate(item);
                    }
                    else if (item.CellStyle != null && item.CellStyle.DataFormat == 176)
                    {
                        return GetCellValueByDate(item);
                    }
                    else
                    { 
                        return item.NumericCellValue;
                    }

                case CellType.String:
                    string strValue = item.StringCellValue;
                    return strValue?.Trim();

                case CellType.Unknown:
                case CellType.Blank:
                default:
                    return string.Empty;
            }
        }

        public static object GetCellValueByDate(ICell item)
        {
            try
            {
                var sts = item.DateCellValue.ToString("yyyy-MM-dd");
                var pps = Convert.ToDateTime(sts);
                var b = DateTime.Now.AddMonths(-1);
                var e = DateTime.Now.AddMonths(1);
                if (pps < b || pps > e)
                {
                    return item.NumericCellValue;
                }

                return pps;
            }
            catch
            {
                return item.NumericCellValue;
            }           
        }
    }
}
